package com.kis.excel;

import com.kis.ExcelObject;
import com.kis.ExcelObjectQ;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.LinkedList;
import java.util.List;

/**
 * Created by yks on 15-11-27.
 */
public class ReadExcelQ {
    public static List<ExcelObjectQ> xls2String(File file) {
        String result = "";
        List<ExcelObjectQ> strings= new LinkedList<ExcelObjectQ>();
        try {
            FileInputStream fis = new FileInputStream(file);
            Workbook rwb = Workbook.getWorkbook(fis);
            Sheet sheet1 = rwb.getSheet(0);
            for (int i = 3; i < 35; i++) {
                String type= sheet1.getRow(i)[1].getContents();
                String country= sheet1.getRow(i)[2].getContents();
                String code= sheet1.getRow(i)[3].getContents();
                String name = sheet1.getRow(i)[9].getContents();
                if(code.indexOf(",")>-1) {
                    String[] codes = code.split(",");
                    for (String s : codes) {
                        if(type.indexOf(",")>-1){
                            String[] types = type.split(",");
                            for (String type1 : types) {
                                ExcelObjectQ excelObjectQ = new ExcelObjectQ(name+getPackageType(type1),country,s);
                                strings.add(excelObjectQ);
                            }
                        }else{
                            ExcelObjectQ excelObjectQ = new ExcelObjectQ(name+getPackageType(type),country,s);
                            strings.add(excelObjectQ);
                        }
                    }
                }
            }
            fis.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return strings;
    }
    public static String getPackageType(String type){
        String returnType = null;
        if(type.equals("0"))returnType="平邮";
        if(type.equals("1"))returnType="挂号";
        return returnType;
    }
    public static void writeExcel(List<ExcelObjectQ> excelObjects,String path){
        WritableWorkbook wwb = null;
        try {
            wwb = Workbook.createWorkbook(new File(path));
            WritableSheet wSheet = wwb.createSheet("sheet1", 0);
            for (int i=0;i<excelObjects.size();i++) {
                Label label0 = new Label(0,i, excelObjects.get(i).getType());
                Label label1 = new Label(1,i, excelObjects.get(i).getCountry());
                Label label2 = new Label(2,i, excelObjects.get(i).getCode());
                wSheet.addCell(label0);
                wSheet.addCell(label1);
                wSheet.addCell(label2);
            }
            wwb.write();
            wwb.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (RowsExceededException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }
    public static void main(String[] args) {
        File file = new File("/home/yks/r.xls");
        List<ExcelObjectQ> strings= xls2String(file);
//        for (ExcelObject excelObject : excelObjects) {
//            System.out.println(excelObject.getCode());
//        }
        writeExcel(strings,"/home/yks/w1.xls");
    }
}

